<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Wus extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

	public function debug($no){
		$this->db->query("INSERT INTO utl_debug (debug_no,debug_msg) VALUES ('".$no."','".date('Y-m-d H:i:S')."')");
	}


	public function get_status($date,$id) {
		if($date == 0){
		$query = $this->db->query("SELECT * FROM ubs_billing_prog_mv WHERE yearMonth = (Select MAX(yearMonth) FROM ubs_billing_prog_mv WHERE company_id = '".$id."')  AND company_id = '".$id."' ");
		}else{
		$query = $this->db->query("SELECT * FROM ubs_billing_prog_mv WHERE yearMonth = '".$date."' AND company_id = '".$id."' ");
		}
		return $query->result();

	}

	public function get_status_col($date,$id) {
		if($date == 0){
		$query = $this->db->query("SELECT * FROM mv_collection_prog WHERE yearMonth = (Select MAX(yearMonth) FROM mv_collection_prog WHERE company_id = '".$id."') AND company_id = '".$id."' ");
		}else{
		$query = $this->db->query("SELECT * FROM mv_collection_prog WHERE yearMonth = '".$date."' AND company_id = '".$id."' ");
		}
		return $query->result();
	}

	public function get_HY_rev($date,$id){
		$query = $this->db->query("SELECT * FROM mv_collection_prog WHERE (yearMonth BETWEEN '".$date."' AND (Select MAX(yearMonth) FROM mv_collection_prog)) AND company_id = '".$id."' ");
		return $query->result();
	}

	public function get_HY_col($date,$id){
		$query = $this->db->query("SELECT 	sum(payment_amt) amt 
		,DATE_FORMAT(receipt_date,'%Y-%m') ym
        ,(SELECT company_id FROM gl_company WHERE company_id = '".$this->session->userdata('company_id')."') company_id 
     FROM ar_receipt   WHERE (DATE_FORMAT(receipt_date,'%Y-%m') BETWEEN '".$date."' AND (Select MAX(DATE_FORMAT(receipt_date,'%Y-%m')) 
                                                          FROM ar_receipt )  )
    GROUP BY DATE_FORMAT(receipt_date,'%Y-%m')");
		return $query->result();
	
		
	}


	public function login($data) {
		$this->db->where('user_name', $data['user_name']);
		$this->db->where('pwd', password_verify($data['password'], 'pwd'));
		$this->db->limit(1);
		$query = $this->db->get('app_users_v');
		if ($query->num_rows() == 1) {
			return $query->result();
		} else {
			return $query->result();
		}
	}

	public function login_cust($data) {
		$this->db->where('email', $data['user_name']);
		$this->db->where('password', password_verify($data['password'], 'password'));
		$this->db->limit(1);
		$query = $this->db->get('ar_customer_accounts');
		if ($query->num_rows() == 1) {
			return $query->result();
		} else {
			return $query->result();
		}
	}

	public function resetpass($data){
		$this->db->where('user_id',$data['updated_by']);
		$q = $this->db->update('app_users',$data);
		$rows = $this->db->affected_rows();
		if($rows == 1){
			echo json_encode(array('msg' => 'success'));
		}else{
			echo json_encode(array('msg' => 'fail'));
		}
	}

	public function get_details($num){
		//$this->db->where('entity_id',$num);
		//$this->db->select('entity_name');
		$q = $this->db->query('SELECT entity_name FROM ar_entity WHERE entity_id = '.$num);
		return $q->result();
	}

//$2y$10$Bhw044LEjsakAbq57CrG/eVwjSdtBDYOpywgiU6Hk6qY905bhbqU6
	public function getAccess($id) {
		$sql = "SELECT af.form_id
                   , af.module_id module_id
                   , af.menu_id menu_id
                   , aaur.allow_create create_e
                   , aaur.allow_update update_e
                   , aaur.allow_delete delete_e
                   , aaur.read_only read_e
                  FROM app_forms af
                    INNER JOIN app_active_user_role_v aaur ON aaur.form_id = af.form_id
                  WHERE aaur.user_id = '" . $id . "'
                  ORDER BY af.module_id ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function get_form_name($id) {
		$query = $this->db->select("exe_file_name,program_type,form_name")
			->from('app_forms')
			->where('form_id', $id)
			->limit(1)
			->get();

		return $query->result();
	}
	public function get_mod_name($id) {
		$query = $this->db->select("dir_path,module_name")
			->from('app_modules')
			->where('module_id', $id)
			->limit(1)
			->get();

		return $query->result();
	}
	public function get_menu_name($id) {
		$query = $this->db->select("menu_name")
			->from('app_module_menu')
			->where('menu_id', $id)
			->limit(1)
			->get();

		return $query->result();
	}

	public function save_module($data) {
		$this->db->query("call app_modules_ins(?,?,?,?,?,?,?,?,@id,@err,@msg)", $data);
		$que = $this->db->query("SELECT @id as id,@err as err,@msg as msg");
		$id;
		$msg;
		$err;
		foreach ($que->result() as $keys) {
			$id = $keys->id;
			$err = $keys->err;
			$msg = $keys->msg;
		}
		$arr = array(
			'modID' => $id,
			'err' => $err,
			'msg' => $msg,
		);
		echo json_encode($arr);
	}
	public function edit_module($data) {
		$this->db->query("call app_modules_upd(?,?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as err,@msg as msg");
		$id;
		$msg;
		foreach ($que->result() as $keys) {
			$id = $keys->err;
			$msg = $keys->msg;
		}
		$arr = array(
			'modID' => $id,
			'msg' => $msg,
		);
		echo json_encode($arr);
	}

	public function edit_menus($data) {
		$this->db->query("call app_module_menu_upd(?,?,?,@err_code,@err_msg)", $data);
		// $flag = 0;
		// if ($this->db->affected_rows() > 0) {
		//       $flag = 1;
		//   }
		$err;
		$msg;
		$que = $this->db->query("SELECT @err_code as error, @err_msg as err_msg");
		return $que->result();
		// foreach($que->result() as $keys){
		//     $err = $keys->error;
		//     $msg = $keys->err_msg;
		// }
		// $arr = array(
		//     'error'=>$err,
		//     'err_msg'=>$msg
		// );
		// return $arr;
	}

	public function insertModuleMenu($data) {
		$query = $this->db->query("call app_module_menu_ins(?,?,?,@gID,@err,@msg)", $data);
		$que = $this->db->query("SELECT @gID as id,@err,@msg as msg");
		foreach ($que->result() as $keys) {
			$id = $keys->id;
			$msg = $keys->msg;
		}
		$this->db->where('menu_id', $id);
		$query = $this->db->get('app_module_menu');
		foreach ($query->result() as $keys) {
			$name = $keys->menu_name;
		}
		$arr = array(
			'modID' => $id,
			'msg' => $name,
		);
		return $arr;
	}
	public function getprogGroup() {
		$query = $this->db->select("group_id,module_id,group_name")
			->from('app_program_groups')
			->get();
		return $query->result();
	}

	public function getModuleMenus() {
		$query = $this->db->select("*")
			->from('app_module_menu')
			->get();
		return $query->result();
	}

	public function getForms() {
		$query = $this->db->select('*')
			->from('app_forms_v')
			->group_by("form_name")
			->get();
		return $query->result();
	}

	public function getModName() {
		$query = $this->db->select("module_id,module_code,module_name,dir_path")
			->from('app_modules')
			->group_by("module_name")
			->get();

		return $query->result();
	}
	public function getModuleFormMenu($id) {
		$sql = "SELECT amm.menu_name menu_name,amm.menu_id menu_id
              FROM app_modules am
                INNER JOIN app_module_menu amm ON am.module_id = amm.module_id
              WHERE am.module_id = " . $id . "
";
		$query = $this->db->query($sql);
		return $query->result();
	}
	public function searchModuleDB($modulecode, $modulename) {
		$sql;
		if ($modulecode != "" && $modulename != "") {
			$sql = "SELECT * FROM app_modules WHERE module_code LIKE " . $this->db->escape($modulecode) . " AND module_name LIKE '" . $modulename . "' ";
		} else if ($modulecode == "" && $modulename != "") {
			$sql = "SELECT * FROM app_modules WHERE module_name LIKE " . $this->db->escape($modulename) . " ";
		} else {
			$sql = "SELECT * FROM app_modules WHERE module_code LIKE " . $this->db->escape($modulecode) . " ";
		}
		//$query = $this->db->get_where('app_modules', array('module_code' => $modulecode, 'module_name' => $modulename), 1);
		$query = $this->db->query($sql);
		if ($query->num_rows() != 0) {
			return $query->result();
		} else {
			$result = "No results found.";
			return $result;
		}
	}

	public function getModuleMenu($id) {

		$this->db->where('module_id', $id);
		$query = $this->db->get('app_module_menu');
		return $query->result();
	}

	public function getModuleMenuID($id) {

		$this->db->where('menu_id', $id);
		$query = $this->db->get('app_module_menu');
		return $query->result();
	}

	public function getPG($id) {
		$query = $this->db->select('*')
			->from('app_program_groups')
			->where('module_id', $id)
			->get();
		return $query->result();
	}

	public function getMod() {
		$query = $this->db->select('*')
			->from('app_modules')
			->get();
		return $query->result();
	}

	public function getLookupValues() {
		$query = $this->db->select('*')
			->from('app_lookup_values')
			->get();
		return $query->result();
	}

	public function get_lookup_values_by_type($type){
		return $this->db->query('SELECT * FROM app_lookup_values WHERE lookup_type = "'.$type.'" AND enable_flag = "Y"' )->result();
	}

	public function get_lookup_values_by_code($code){
		return $this->db->query('SELECT * FROM app_lookup_values WHERE lookup_code = "'.$code.'" AND enable_flag = "Y"')->result();
	}

	public function insertRoleData($data) {
		$this->db->query("call app_roles_ins(?,?,?,?,?,@gID,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg, @gID as id");
		return $que->result();
	}

	public function getModbyID($id) {
		$query = $this->db->select('*')
			->from('app_modules')
			->where('module_id', $id)
			->get();
		return $query->result();
	}

	public function getPath($module_id, $group_id, $progID) {
		// $query = $this->db->query("SELECT am.dir_path directoryPath, apg.group_name name
		//   FROM app_modules am
		//   INNER JOIN app_program_groups apg ON am.module_id = $id ");
		$this->db->select('app_modules.dir_path, app_program_groups.group_name, app_programs.exe_file_name');
		$this->db->from('app_modules');
		$this->db->join('app_program_groups', 'app_program_groups.module_id = app_modules.module_id');
		$this->db->join('app_programs', 'app_modules.module_id = app_programs.module_id AND app_program_groups.group_id = app_programs.group_id');
		$this->db->where('app_modules.module_id', $module_id);
		$this->db->where('app_program_groups.group_id', $group_id);
		$this->db->where('app_programs.program_id', $progID);

		$query = $this->db->get();
		return $query->result();
	}

	public function getGroupName($id) {
		$query = $this->db->select('*')
			->from('app_program_groups')
			->where('group_id', $id)
			->get();
		return $query->result();
	}

	public function getFormModuleMenu($code) {
		$this->db->select('app_modules.module_id,app_modules.module_name, app_module_menu.menu_id, app_module_menu.menu_name, app_forms.form_id,app_forms.form_code, app_forms.form_name');
		$this->db->from('app_modules');
		$this->db->join('app_module_menu', 'app_module_menu.module_id = app_modules.module_id');
		$this->db->join('app_forms', 'app_modules.module_id = app_forms.module_id');
		$this->db->where('app_forms.form_code', $code);
		$query = $this->db->get();
		return $query->result();
	}

	public function insertFormFunction($data) {

		$this->db->query("call app_form_functions_dml(?,?,?,?,?,?,?,?,@gID,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		foreach ($que->result() as $key) {
			echo $key->error;
			echo $key->msg;
		}

	}

	public function insertuom($data) {
		$this->db->query("call inv_uom_dml(?,?,?,?,?,?,@err,@msg)", $data);
		$que = $this->db->query("SELECT @err as error , @msg as msg");
		// foreach ($que->result() as $key) {
		//     echo $key->error;
		//     echo $key->msg;
		// }
		echo json_encode($que->result());
	}

	public function getFormFunctions() {

		$this->db->select('app_modules.module_id, app_module_menu.menu_id,app_form_functions.form_id,app_form_functions.form_function_id , app_form_functions.display_name');
		$this->db->from('app_modules');
		$this->db->join('app_module_menu', 'app_module_menu.module_id = app_modules.module_id');
		$this->db->join('app_form_functions', 'app_module_menu.menu_id = app_form_functions.menu_id');
		$this->db->join('app_forms', 'app_forms.form_id = app_form_functions.form_id');
		$query = $this->db->get();
		return $query->result();
	}

	public function getPathPage($id) {

		$this->db->select('app_modules.module_id, app_modules.dir_path, app_forms.exe_file_name, app_module_menu.menu_id,app_form_functions.form_id,app_form_functions.form_function_id, app_form_functions.display_name');
		$this->db->from('app_modules');
		$this->db->join('app_module_menu', 'app_module_menu.module_id = app_modules.module_id');
		$this->db->join('app_form_functions', 'app_module_menu.menu_id = app_form_functions.menu_id');
		$this->db->join('app_forms', 'app_forms.form_id = app_form_functions.form_id');
		$this->db->where('app_form_functions.form_function_id', $id);
		$query = $this->db->get();
		return $query->result();
	}

	public function getFormFunctionName($name) {
		$this->db->where('display_name', $name);
		$query = $this->db->get('app_form_functions_v');
		return $query->result();

	}

	public function getModuleList($name) {

		$this->db->where('module_name', $name);
		$query = $this->db->get('app_modules');
		return $query->result();

	}

	public function getMenuList($name) {

		$this->db->where('menu_name', $name);
		$query = $this->db->get('app_module_menu');
		return $query->result();

	}

	public function insertRoleDtl($data) {

		$this->db->query('call app_role_dtl_ins(?,?,?,?,?,@err,@msg)', $data);
		$query = $this->db->query("SELECT @err as error , @msg as msg");
		return $query->result();
	}

	public function getRoleDtl($id) {
		$this->db->where('role_id', $id);
		$query = $this->db->get('app_role_details_v');
		return $query->result();

	}

	public function get_form_roles($id) {
		//$this->db->where('enable_flag', 'Y');
		$this->db->where('form_id', $id);
		$this->db->where('user_id', $this->session->userdata('user_id'));
		$this->db->select('form_id,allow_create,allow_update,allow_delete,read_only');
		$this->db->from('app_user_access_v');
		$query = $this->db->get();
		return $query->result();
	}

	public function call_acct_chg(){
		$this->db->query('call ubs_create_acct_charges()');
	}

	public function register($data){
		$this->db->query('call register(?,?,?,?,@err,@msg)', $data);
		$query = $this->db->query("SELECT @err as error , @msg as msg");
		return $query->result();
	}	

	public function checkmails($email){
		$this->db->where('user_name',$email);
		$q = $this->db->get('app_users');
		if($q->num_rows() > 1){
			return true;
		}else{
			return false;
		}
	}
	public function checkmail($email){
		$this->db->where('user_name',$email);
		$q = $this->db->get('app_users');
		if($q->num_rows() > 0){
			return true;
		}else{
			return false;
		}
	}

	public function check_ifexist_an($email){
		$this->db->where('user_name',$email);
		$q = $this->db->get('app_users');
		if($q->num_rows() > 0){
			return true;
		}else{
			return false;
		}
	}

	public function forget($data){
		$this->db->query('call app_users_reset(?,?,@err,@msg)', $data);
		$query = $this->db->query("SELECT @err as error , @msg as msg");
		return $query->result();
	}

	 public function tae(){
	 	//$this->db2 = $this->load->database('db2', TRUE);
		//$this->db->query('INSERT INTO cust_pdf("bill_id") VALUES (2000)');
		$q = $this->db->get('cust_pdf');
		var_dump($q->result());
	 }

	public function get_account($id){
		$q = $this->db->query("SELECT aa.acct_num, aa.acct_name, aea.zone_1 FROM ar_account aa
							   INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
							   WHERE aa.account_id = ".$id);
		return $q->result();
	}

	public function save_pdf($data){
		$this->db->query('CALL cust_pdf(?,?,?,?,?,?,?,?,?)', $data);
		$result = $this->db->query('SELECT @a as status');
		$status;
		foreach($result->result() as $key){
			$status = $key->status;
		}
		return $status;
		if (empty($this->db->_error_message())) {
		    echo "FAIL";
		}
	
	}

	public function get_inv($id){
    	$sql = "SELECT uil.line_amt,uil.charge_code,uil.line_num,ui.invoice_id FROM ubs_invoices ui,ubs_invoice_lines uil
    			WHERE uil.invoice_id = ui.invoice_id
    			AND ui.invoice_num = ".$id;
    	$q = $this->db->query($sql);
    	return $q->result();
    }
    
    public function if_exist($id){
    	$q = $this->db->query('SELECT bill_id FROM cust_pdf WHERE bill_id = '.$id);
    	return $q->result();
    }
    
     public function get_bal($id){
		$sql = "SELECT total_due as curchg FROM ubs_meter_readings WHERE bill_id = ".$id." LIMIT 1";
    	$q = $this->db->query($sql);
    	return $q->result();
    }

    public function get_pay($id,$date1,$date2){
    	// DATE_SUB(STR_TO_DATE('".$date1."', '%d-%b-%y'),INTERVAL 1 DAY)
    	// ap.account_id IN (SELECT account_id FROM ar_account WHERE acct_num = ".$id.") AND
    	// ap.invoice_id IN (SELECT invoice_id FROM ubs_invoices WHERE invoice_num = ".$id.")
		$sql = "SELECT ap.pay_amount, ar.receipt_date from ar_payments ap INNER JOIN ar_receipt ar ON ap.receipt_id = ar.receipt_id WHERE ap.account_id IN (SELECT account_id FROM ar_account WHERE acct_num = ".$id.") AND ar.receipt_date BETWEEN str_to_date('".$date1."', '%d-%b-%y') AND DATE_SUB(STR_TO_DATE('".$date2."', '%d-%b-%y'),INTERVAL 1 DAY)";
    	$q = $this->db->query($sql);
    	return $q->result();
    }

    public function debugers($num,$amount,$cur)
    {
      $this->db->query("INSERT INTO utl_debug(debug_no,debug_msg) VALUES ('".$num."','Negatives ".$amount.' '.$cur."')");
    }

     

}